﻿IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'vw_medianamik_UrlXmlDoc' AND COLUMN_NAME = 'LinkOnFirstChild')
BEGIN
	declare @sql nvarchar(4000), @guid varchar(50), @propertyName varchar(50)
	SET @guid = '61E381F0-0F89-4264-8830-FF5D82C6C1A0'
	SET @propertyName = 'url'
	set @sql = 
	'
	ALTER VIEW [dbo].[vw_medianamik_UrlXmlDoc]
AS
    WITH    Node
              AS ( SELECT   npv1.NodeId ,
                            n.TypeId ,
                            n.[CreatedOn] ,
                            n.[Path] ,
                            n.[Depth] ,
                            n.[Position] ,
                            n.[ParentNodeId] ,
                            npv1.CultureName ,
                            npv1.Revision ,
                            CAST(isnull(npv3.VALUE, 0) AS bit) AS LinkOnFirstChild ,
                            CAST(npv1.Value AS XML) AS Xml ,
                            CASE WHEN npv1.Revision IS NULL THEN -1
                                 ELSE COUNT(npv2.Revision) - 1
                            END AS Generation ,
                            ni.IsActive
                   FROM     dbo.medianamik_NodePropertyValue AS npv1
                            INNER JOIN dbo.medianamik_Node AS n ON n.NodeId = npv1.NodeId
                            INNER JOIN dbo.medianamik_Property AS p ON p.PropertyId = npv1.PropertyId
                            INNER JOIN dbo.medianamik_NodeInstance AS ni ON ni.NodeId = npv1.NodeId
                                                              AND ni.CultureName = npv1.CultureName
                            LEFT OUTER JOIN dbo.medianamik_NodePropertyValue
                            AS npv2 ON p.PropertyId = npv2.PropertyId
                                       AND npv1.NodeId = npv2.NodeId
                                       AND npv1.CultureName = npv2.CultureName
                             LEFT OUTER JOIN dbo.medianamik_NodePropertyValue
                            AS npv3 ON npv3.PropertyId = ''' + @guid + '''
                                       AND npv1.NodeId = npv3.NodeId
                                       AND npv1.CultureName = npv3.CultureName
                   WHERE    ( p.Name = ''' + @propertyName + ''' )
                            AND ( n.IsDeleted = 0 )
                            AND ( ni.IsActive = 1 )
                            AND ( ISNULL(npv2.Revision,
                                         dbo.fn_medianamik_MaxRevision()) >= ISNULL(npv1.Revision,
                                                              dbo.fn_medianamik_MaxRevision()) )
                   GROUP BY npv1.NodeId ,
                            n.TypeId ,
                            n.[CreatedOn] ,
                            n.[Path] ,
                            n.[Depth] ,
                            n.[Position] ,
                            n.[ParentNodeId] ,
                            npv1.CultureName ,
                            npv1.Revision ,
                            npv1.Value ,
                            ni.IsActive,
                            npv3.Value
                 )
    SELECT  NodeId ,
            TypeId ,
            [CreatedOn] ,
            [Path] ,
            [Depth] ,
            [Position] ,
            [ParentNodeId] ,
            CultureName ,
            Revision ,
            Xml ,
            LinkOnFirstChild,
            Generation
    FROM    Node AS Node_1
    WHERE   ( Generation <= 0 )
            AND ( IsActive = 1 )
        '
        
        exec sp_executesql @sql
END

   
   